SQL Intersect by ANDing IN Clauses

Mark Leighton Fisher on 2007-11-09T17:51:56

Set intersect in SQL does not require an INTERSECT operator. To express a set intersection in SQL, you can AND together IN clauses. This makes sense, as one way to describe a set intersection is to say:

"... it is a member of one set AND another set AND another set AND so on ..."

How do you express "a member of one set"? By saying "WHERE field IN list_or_subselect". The resulting SQL for a set intersection operation looks like:

 SELECT
  field1, ...
 FROM
  table1, ...
 WHERE
  field_of_interest IN (SELECT fieldX ...
 AND
  field_of_interest IN ('x', 'y-2', 42 ...
 AND
  ...